package com.example.j280_webproject.day038;

import java.sql.*;

public class D03701_JDBC {
    /*
        学生存在         1、班级存在   2、班级不存在
        学生不存在       1、班级存在   2、班级不存在
         */
    public static void main(String[] args)  {
        D03701_JDBC d03701_jdbc = new D03701_JDBC();
        try {
//            d03701_jdbc.inserStudentAddClass("周柯","9年级","11");  //学生不存在  班级不存在
//            d03701_jdbc.inserStudentAddClass("周柯","9年级","11");  //学生存在  班级存在
//            d03701_jdbc.inserStudentAddClass("周柯","8年级","11");  //学生存在  班级不存在
            d03701_jdbc.inserStudentAddClass("周柯","8年级","12");  //学生不存在  班级存在
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            System.out.println("系统繁忙请稍后再试！");
        }
    }
    public void inserStudentAddClass(String studentName,String className,String card) throws ClassNotFoundException, SQLException {
        String driver = "com.mysql.jdbc.Driver";    //固定写法
        Class.forName(driver);                      //通过反射机制 把指定的类加载到当前类中

        String url = "jdbc:mysql://localhost:3306/mygxa?useSSL=false&characterEncoding=UTF-8";
        String db_user = "root";
        String db_password = "root";
        Connection conn = DriverManager.getConnection(url, db_user, db_password);
        System.out.println("连接成功");
        conn.setAutoCommit(false);

        String sql = "select * from student038 where card=?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1,card);
        ResultSet resultSet = preparedStatement.executeQuery();
        /*
        学生存在         1、班级存在   2、班级不存在
        学生不存在       1、班级存在   2、班级不存在
         */
        //判断学生是否存在
        if(resultSet.next()){                                              //如果学生存在则为true执行if里面的代码块
            int student_id = resultSet.getInt("id");
            String studentName_result=resultSet.getString("studentName");
            if (!studentName_result.equals(studentName)){
                System.out.println("请输入正确的学生信息！");
                return;
            }
            sql="select id from class where className=?";                  //查询班级是否存在
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,className);
            ResultSet resultSet1 = preparedStatement.executeQuery();
            if (resultSet1.next()){                                        //如果next为true则班级存在执行if里面的代码块
                int class_id = resultSet1.getInt("id");
                sql="update student038 set class_id=? where id=?";         //更新学生的班级信息
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setInt(1,class_id);
                preparedStatement.setInt(2,student_id);
                preparedStatement.execute();
            }
            else {                                                         //班级不存在
                sql = "insert into class(className) values (?)";           //插入班级名称
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,className);
                preparedStatement.execute();

                sql = "select id from class where className=?";            //查询插入班级名称
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,className);
                ResultSet resultSet01 = preparedStatement.executeQuery();
                resultSet01.next();
                int class_id = resultSet01.getInt("id");       //获取插入班级编号

                sql="update student038 set class_id=? where id=?";          //将班级编号插入学生表对应班级编号
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setInt(1,class_id);
                preparedStatement.setInt(2,student_id);
                preparedStatement.execute();
            }
        }
        else {
            sql = "insert into student038(studentName, card) values (?,?)";  //插入学生的姓名和身份证号码
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,studentName);
            preparedStatement.setString(2,card);
            preparedStatement.execute();

            sql = "select id from student038 where card=?";                   //查询学生插入是否成功
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,card);
            ResultSet resultSet01 = preparedStatement.executeQuery();
            resultSet01.next();
            int student_id = resultSet01.getInt("id");            //获取插入学生id

            sql="select id from class where className=?";                      //查询班级是否存在
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,className);
            ResultSet resultSet1 = preparedStatement.executeQuery();
            if (resultSet1.next()){                                            //如果next为true则班级存在执行if里面的代码块
                int class_id = resultSet1.getInt("id");
                sql="update student038 set class_id=? where id=?";             //更新学生班级名称
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setInt(1,class_id);
                preparedStatement.setInt(2,student_id);
                preparedStatement.execute();
            }
            else {
                sql = "insert into class(className) values (?)";
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,className);
                preparedStatement.execute();

                sql = "select id from class where className=?";
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,className);
                ResultSet resultSet02 = preparedStatement.executeQuery();
                resultSet02.next();
                int class_id = resultSet02.getInt("id");

                sql="update student038 set class_id=? where id=?";          //将班级编号插入学生表对应班级编号
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setInt(1,class_id);
                preparedStatement.setInt(2,student_id);
                preparedStatement.execute();
            }
        }

        conn.commit();
        preparedStatement.close();
        conn.close();
    }
}
